<!DOCTYPE html>
<html lang='en'><head>
  <title>Sql使用手记 | 江小凡的博客</title>
  <meta charset='utf-8'>
  <meta name="generator" content="Hugo 0.76.5" />
  <meta name = 'viewport' content = 'width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no'>
  <meta http-equiv = 'X-UA-Compatible' content = 'IE=edge'>
<meta property = 'og:locale' content = 'en_US' />
<meta property="og:type" content="article">
<meta property = 'og:title' content = 'sql使用手记' />
<meta name="description" content="[toc]
​    Sql同样是一门编程语言，在某种意义上来讲，大部分的程序员写的代码都可以通过sql实现，sql应当是后端程序员基本功。sql在排查数据，如同脚本一样简单直接高效，非常实用。">
<meta property = 'og:description' content = '[toc]
​    Sql同样是一门编程语言，在某种意义上来讲，大部分的程序员写的代码都可以通过sql实现，sql应当是后端程序员基本功。sql在排查数据，如同脚本一样简单直接高效，非常实用。'>
<meta property = 'og:url' content = 'https://jxiaof.com/post/%E8%B0%88%E8%B0%88%E5%AE%9E%E9%99%85%E4%BD%BF%E7%94%A8sql%E5%BC%80%E5%8F%91/' />
<meta property = 'og:image' content = 'https://jxiaof.com/images/database.png'/>
<meta name = 'twitter:card' content = 'summary_large_image' />
<meta name = 'twitter:creator' content = ''>
<meta name = 'twitter:title' content = 'sql使用手记' />
<meta property = 'twitter:description'  content = '[toc]
​    Sql同样是一门编程语言，在某种意义上来讲，大部分的程序员写的代码都可以通过sql实现，sql应当是后端程序员基本功。sql在排查数据，如同脚本一样简单直接高效，非常实用。'/>
<meta name = 'twitter:image' content = 'https://jxiaof.com/images/database.png' />
<link rel='apple-touch-icon' sizes='180x180' href='https://jxiaof.com/images/icons/apple-touch-icon.png'>
<link rel='icon' type='image/png' sizes='32x32' href='https://jxiaof.com/images/icons/favicon-32x32.png'>
<link rel='icon' type='image/png' sizes='16x16' href='https://jxiaof.com/images/icons/favicon-16x16.png'>
<link rel='manifest' href='https://jxiaof.com/images/icons/site.webmanifest'>

  <link rel='canonical' href='https://jxiaof.com/post/%E8%B0%88%E8%B0%88%E5%AE%9E%E9%99%85%E4%BD%BF%E7%94%A8sql%E5%BC%80%E5%8F%91/'>
  <link rel = 'stylesheet' href = 'https://jxiaof.com/css/styles.b934c9412cc837b60543673c5f879b59569b1c7bea59b56896858a3b0a15220c8dbd91e80317b591a1857e31aa26b3bf8fbaca17c5bab12ff1231a272cd6bd6d.css' integrity = 'sha512-uTTJQSzIN7YFQ2c8X4ebWVabHHvqWbVoloWKOwoVIgyNvZHoAxe1kaGFfjGqJrO/j7rKF8W6sS/xIxonLNa9bQ=='>
</head>

  <body><div class = 'nav-drop'>
  <div class = 'nav-body'>
      <a href = 'https://jxiaof.com/about/' class = 'nav_item'>About</a>
    <div class = 'nav-close'></div>
  </div>
</div><header class = 'nav' >
  <nav class = 'nav-menu'>
    <a href='https://jxiaof.com/' class = 'nav-brand nav_item'>江小凡的博客</a>
    <div class = 'nav_bar-wrap'>
      <div class = 'nav_bar'></div>
    </div>
  </nav>
</header>


    <main>
<section class = 'post_header' style = 'background-image:url(https://jxiaof.com/images/database.png);'>
  <h1 class='post_title'>sql使用手记</h1>
</section>
<div class = 'post'>
  <article class='post_content'><p>[toc]</p>
<p>​    Sql同样是一门编程语言，在某种意义上来讲，大部分的程序员写的代码都可以通过sql实现，sql应当是后端程序员基本功。sql在排查数据，如同脚本一样简单直接高效，非常实用。</p>
<p>首先写好注释</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#75715e">-- 注释
</span><span style="color:#75715e"></span>
<span style="color:#75715e">/* 注释 */</span>
</code></pre></div><h2 id="cast">cast</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span> 
 <span style="color:#66d9ef">CASE</span> 
 <span style="color:#66d9ef">WHEN</span> http_user_agent <span style="color:#66d9ef">like</span> <span style="color:#e6db74">&#39;%android%&#39;</span> <span style="color:#66d9ef">then</span> <span style="color:#e6db74">&#39;android&#39;</span> 
 <span style="color:#66d9ef">WHEN</span> http_user_agent <span style="color:#66d9ef">like</span> <span style="color:#e6db74">&#39;%ios%&#39;</span> <span style="color:#66d9ef">then</span> <span style="color:#e6db74">&#39;ios&#39;</span> 
 <span style="color:#66d9ef">ELSE</span> <span style="color:#e6db74">&#39;unknown&#39;</span> <span style="color:#66d9ef">END</span>  
 <span style="color:#66d9ef">as</span> http_user_agent,
    <span style="color:#66d9ef">count</span>(<span style="color:#ae81ff">1</span>) <span style="color:#66d9ef">as</span> pv 
    <span style="color:#66d9ef">group</span> <span style="color:#66d9ef">by</span>  http_user_agent
</code></pre></div><div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#f92672">*</span> <span style="color:#f92672">|</span> <span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">`</span>
 <span style="color:#66d9ef">CASE</span>
 <span style="color:#66d9ef">WHEN</span> latency <span style="color:#f92672">&lt;</span> <span style="color:#ae81ff">10</span> <span style="color:#66d9ef">then</span> <span style="color:#e6db74">&#39;s10&#39;</span>
 <span style="color:#66d9ef">WHEN</span> latency <span style="color:#f92672">&lt;</span> <span style="color:#ae81ff">100</span> <span style="color:#66d9ef">then</span> <span style="color:#e6db74">&#39;s100&#39;</span>
 <span style="color:#66d9ef">WHEN</span> latency <span style="color:#f92672">&lt;</span> <span style="color:#ae81ff">1000</span> <span style="color:#66d9ef">then</span> <span style="color:#e6db74">&#39;s1000&#39;</span>
 <span style="color:#66d9ef">WHEN</span> latency <span style="color:#f92672">&lt;</span> <span style="color:#ae81ff">10000</span> <span style="color:#66d9ef">then</span> <span style="color:#e6db74">&#39;s10000&#39;</span>
 <span style="color:#66d9ef">else</span> <span style="color:#e6db74">&#39;s_large&#39;</span> <span style="color:#66d9ef">end</span>
 <span style="color:#66d9ef">as</span> latency_slot,
 <span style="color:#66d9ef">count</span>(<span style="color:#ae81ff">1</span>) <span style="color:#66d9ef">as</span> pv
 <span style="color:#66d9ef">group</span> <span style="color:#66d9ef">by</span> latency_slot
</code></pre></div><blockquote>
<p>if语法逻辑上等同于CASE WHEN语法。</p>
</blockquote>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">CASE</span>
     <span style="color:#66d9ef">WHEN</span> condition <span style="color:#66d9ef">THEN</span> true_value
     [ <span style="color:#66d9ef">ELSE</span> false_value ]
 <span style="color:#66d9ef">END</span>
</code></pre></div><div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#75715e">-- 查询所有学生的姓名和性别(条件运算)
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> stuname <span style="color:#66d9ef">as</span> <span style="color:#960050;background-color:#1e0010">姓名</span>, <span style="color:#66d9ef">case</span> stusex <span style="color:#66d9ef">when</span> <span style="color:#ae81ff">1</span> <span style="color:#66d9ef">then</span> <span style="color:#e6db74">&#39;男&#39;</span> <span style="color:#66d9ef">else</span> <span style="color:#e6db74">&#39;女&#39;</span> <span style="color:#66d9ef">end</span> <span style="color:#66d9ef">as</span> <span style="color:#960050;background-color:#1e0010">性别</span> <span style="color:#66d9ef">from</span> tb_student;
<span style="color:#66d9ef">select</span> stuname <span style="color:#66d9ef">as</span> <span style="color:#960050;background-color:#1e0010">姓名</span>, <span style="color:#66d9ef">if</span>(stusex, <span style="color:#e6db74">&#39;男&#39;</span>, <span style="color:#e6db74">&#39;女&#39;</span>) <span style="color:#66d9ef">as</span> <span style="color:#960050;background-color:#1e0010">性别</span> <span style="color:#66d9ef">from</span> tb_student;

</code></pre></div><h2 id="日期函数">日期函数</h2>
<h3 id="日期时间类型">日期时间类型</h3>
<ul>
<li>unixtime：以int类型表示从1970年1月1日开始的秒数，例如<code>1512374067</code>表示的时间是<code>Mon Dec 4 15:54:27 CST 2017</code>。日志服务每条日志中内置的时间<code>__time__</code>即为这种类型。</li>
<li>timestamp类型：以字符串形式表示时间，例如<code>2017-11-01 13:30:00</code>。</li>
</ul>
<p>日志服务支持的常见日期函数如下：</p>
<table>
<thead>
<tr>
<th style="text-align:left">函数名</th>
<th style="text-align:left">含义</th>
<th style="text-align:left">样例</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:left"><code>current_date</code></td>
<td style="text-align:left">当天日期。</td>
<td style="text-align:left"><code>latency&gt;100| select current_date</code></td>
</tr>
<tr>
<td style="text-align:left"><code>current_time</code></td>
<td style="text-align:left">当前时间。</td>
<td style="text-align:left"><code>latency&gt;100| select current_time</code></td>
</tr>
<tr>
<td style="text-align:left"><code>current_timestamp</code></td>
<td style="text-align:left">结合current_date 和current_time的结果。</td>
<td style="text-align:left"><code>latency&gt;100| select current_timestamp</code></td>
</tr>
<tr>
<td style="text-align:left"><code>current_timezone()</code></td>
<td style="text-align:left">返回时区。</td>
<td style="text-align:left"><code>latency&gt;100| select current_timezone()</code></td>
</tr>
<tr>
<td style="text-align:left"><code>from_iso8601_timestamp(string)</code></td>
<td style="text-align:left">把iso8601时间转化成带时区的时间。</td>
<td style="text-align:left"><code>latency&gt;100| select from_iso8601_timestamp(iso8601)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>from_iso8601_date(string)</code></td>
<td style="text-align:left">把iso8601转化成天。</td>
<td style="text-align:left"><code>latency&gt;100| select from_iso8601_date(iso8601)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>from_unixtime(unixtime)</code></td>
<td style="text-align:left">把unix时间转化为时间戳。</td>
<td style="text-align:left"><code>latency&gt;100| select from_unixtime(1494985275) </code></td>
</tr>
<tr>
<td style="text-align:left"><code>from_unixtime(unixtime,string)</code></td>
<td style="text-align:left">以string为时区，把unixtime转化成时间戳。</td>
<td style="text-align:left"><code>latency&gt;100| select from_unixtime (1494985275,'Asia/Shanghai')</code></td>
</tr>
<tr>
<td style="text-align:left"><code>localtime</code></td>
<td style="text-align:left">本地时间。</td>
<td style="text-align:left"><code>latency&gt;100| select localtime </code></td>
</tr>
<tr>
<td style="text-align:left"><code>localtimestamp</code></td>
<td style="text-align:left">本地时间戳。</td>
<td style="text-align:left"><code>latency&gt;100| select localtimestamp </code></td>
</tr>
<tr>
<td style="text-align:left"><code>now()</code></td>
<td style="text-align:left">等同于<code>current_timestamp</code>。</td>
<td style="text-align:left">-</td>
</tr>
<tr>
<td style="text-align:left"><code>to_unixtime(timestamp)</code></td>
<td style="text-align:left">timestamp转化成unixtime。</td>
<td style="text-align:left"><code>*| select to_unixtime('2017-05-17 09:45:00.848 Asia/Shanghai')</code></td>
</tr>
</tbody>
</table>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">mysql<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">SELECT</span> name, birth, <span style="color:#66d9ef">MONTH</span>(birth) <span style="color:#66d9ef">FROM</span> pet;
<span style="color:#f92672">+</span><span style="color:#75715e">----------+------------+--------------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> name     <span style="color:#f92672">|</span> birth      <span style="color:#f92672">|</span> <span style="color:#66d9ef">MONTH</span>(birth) <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">----------+------------+--------------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> Fluffy   <span style="color:#f92672">|</span> <span style="color:#ae81ff">1993</span><span style="color:#f92672">-</span><span style="color:#ae81ff">02</span><span style="color:#f92672">-</span><span style="color:#ae81ff">04</span> <span style="color:#f92672">|</span>            <span style="color:#ae81ff">2</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Claws    <span style="color:#f92672">|</span> <span style="color:#ae81ff">1994</span><span style="color:#f92672">-</span><span style="color:#ae81ff">03</span><span style="color:#f92672">-</span><span style="color:#ae81ff">17</span> <span style="color:#f92672">|</span>            <span style="color:#ae81ff">3</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Buffy    <span style="color:#f92672">|</span> <span style="color:#ae81ff">1989</span><span style="color:#f92672">-</span><span style="color:#ae81ff">05</span><span style="color:#f92672">-</span><span style="color:#ae81ff">13</span> <span style="color:#f92672">|</span>            <span style="color:#ae81ff">5</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Fang     <span style="color:#f92672">|</span> <span style="color:#ae81ff">1990</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">27</span> <span style="color:#f92672">|</span>            <span style="color:#ae81ff">8</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Bowser   <span style="color:#f92672">|</span> <span style="color:#ae81ff">1989</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">31</span> <span style="color:#f92672">|</span>            <span style="color:#ae81ff">8</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Chirpy   <span style="color:#f92672">|</span> <span style="color:#ae81ff">1998</span><span style="color:#f92672">-</span><span style="color:#ae81ff">09</span><span style="color:#f92672">-</span><span style="color:#ae81ff">11</span> <span style="color:#f92672">|</span>            <span style="color:#ae81ff">9</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Whistler <span style="color:#f92672">|</span> <span style="color:#ae81ff">1997</span><span style="color:#f92672">-</span><span style="color:#ae81ff">12</span><span style="color:#f92672">-</span><span style="color:#ae81ff">09</span> <span style="color:#f92672">|</span>           <span style="color:#ae81ff">12</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Slim     <span style="color:#f92672">|</span> <span style="color:#ae81ff">1996</span><span style="color:#f92672">-</span><span style="color:#ae81ff">04</span><span style="color:#f92672">-</span><span style="color:#ae81ff">29</span> <span style="color:#f92672">|</span>            <span style="color:#ae81ff">4</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Puffball <span style="color:#f92672">|</span> <span style="color:#ae81ff">1999</span><span style="color:#f92672">-</span><span style="color:#ae81ff">03</span><span style="color:#f92672">-</span><span style="color:#ae81ff">30</span> <span style="color:#f92672">|</span>            <span style="color:#ae81ff">3</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">----------+------------+--------------+
</span><span style="color:#75715e"></span>

mysql<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">SELECT</span> name, birth, CURDATE(),
       TIMESTAMPDIFF(<span style="color:#66d9ef">YEAR</span>,birth,CURDATE()) <span style="color:#66d9ef">AS</span> age
       <span style="color:#66d9ef">FROM</span> pet <span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> age;
<span style="color:#f92672">+</span><span style="color:#75715e">----------+------------+------------+------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> name     <span style="color:#f92672">|</span> birth      <span style="color:#f92672">|</span> CURDATE()  <span style="color:#f92672">|</span> age  <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">----------+------------+------------+------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> Chirpy   <span style="color:#f92672">|</span> <span style="color:#ae81ff">1998</span><span style="color:#f92672">-</span><span style="color:#ae81ff">09</span><span style="color:#f92672">-</span><span style="color:#ae81ff">11</span> <span style="color:#f92672">|</span> <span style="color:#ae81ff">2003</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">19</span> <span style="color:#f92672">|</span>    <span style="color:#ae81ff">4</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Puffball <span style="color:#f92672">|</span> <span style="color:#ae81ff">1999</span><span style="color:#f92672">-</span><span style="color:#ae81ff">03</span><span style="color:#f92672">-</span><span style="color:#ae81ff">30</span> <span style="color:#f92672">|</span> <span style="color:#ae81ff">2003</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">19</span> <span style="color:#f92672">|</span>    <span style="color:#ae81ff">4</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Whistler <span style="color:#f92672">|</span> <span style="color:#ae81ff">1997</span><span style="color:#f92672">-</span><span style="color:#ae81ff">12</span><span style="color:#f92672">-</span><span style="color:#ae81ff">09</span> <span style="color:#f92672">|</span> <span style="color:#ae81ff">2003</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">19</span> <span style="color:#f92672">|</span>    <span style="color:#ae81ff">5</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Slim     <span style="color:#f92672">|</span> <span style="color:#ae81ff">1996</span><span style="color:#f92672">-</span><span style="color:#ae81ff">04</span><span style="color:#f92672">-</span><span style="color:#ae81ff">29</span> <span style="color:#f92672">|</span> <span style="color:#ae81ff">2003</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">19</span> <span style="color:#f92672">|</span>    <span style="color:#ae81ff">7</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Claws    <span style="color:#f92672">|</span> <span style="color:#ae81ff">1994</span><span style="color:#f92672">-</span><span style="color:#ae81ff">03</span><span style="color:#f92672">-</span><span style="color:#ae81ff">17</span> <span style="color:#f92672">|</span> <span style="color:#ae81ff">2003</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">19</span> <span style="color:#f92672">|</span>    <span style="color:#ae81ff">9</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Fluffy   <span style="color:#f92672">|</span> <span style="color:#ae81ff">1993</span><span style="color:#f92672">-</span><span style="color:#ae81ff">02</span><span style="color:#f92672">-</span><span style="color:#ae81ff">04</span> <span style="color:#f92672">|</span> <span style="color:#ae81ff">2003</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">19</span> <span style="color:#f92672">|</span>   <span style="color:#ae81ff">10</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Fang     <span style="color:#f92672">|</span> <span style="color:#ae81ff">1990</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">27</span> <span style="color:#f92672">|</span> <span style="color:#ae81ff">2003</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">19</span> <span style="color:#f92672">|</span>   <span style="color:#ae81ff">12</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Bowser   <span style="color:#f92672">|</span> <span style="color:#ae81ff">1989</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">31</span> <span style="color:#f92672">|</span> <span style="color:#ae81ff">2003</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">19</span> <span style="color:#f92672">|</span>   <span style="color:#ae81ff">13</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Buffy    <span style="color:#f92672">|</span> <span style="color:#ae81ff">1989</span><span style="color:#f92672">-</span><span style="color:#ae81ff">05</span><span style="color:#f92672">-</span><span style="color:#ae81ff">13</span> <span style="color:#f92672">|</span> <span style="color:#ae81ff">2003</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">19</span> <span style="color:#f92672">|</span>   <span style="color:#ae81ff">14</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">----------+------------+------------+------+
</span></code></pre></div><p>查询下个月的数据:</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">mysql<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">SELECT</span> name, birth <span style="color:#66d9ef">FROM</span> pet
       <span style="color:#66d9ef">WHERE</span> <span style="color:#66d9ef">MONTH</span>(birth) <span style="color:#f92672">=</span> <span style="color:#66d9ef">MONTH</span>(DATE_ADD(CURDATE(),INTERVAL <span style="color:#ae81ff">1</span> <span style="color:#66d9ef">MONTH</span>));

mysql<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">SELECT</span> name, birth <span style="color:#66d9ef">FROM</span> pet
       <span style="color:#66d9ef">WHERE</span> <span style="color:#66d9ef">MONTH</span>(birth) <span style="color:#f92672">=</span> <span style="color:#66d9ef">MOD</span>(<span style="color:#66d9ef">MONTH</span>(CURDATE()), <span style="color:#ae81ff">12</span>) <span style="color:#f92672">+</span> <span style="color:#ae81ff">1</span>;
</code></pre></div><h2 id="null-值判断">null 值判断</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span> 
  name, 
  birth, 
  death, 
  TIMESTAMPDIFF(<span style="color:#66d9ef">YEAR</span>,birth,death) <span style="color:#66d9ef">AS</span> age 
<span style="color:#66d9ef">FROM</span> pet 
<span style="color:#66d9ef">WHERE</span> death <span style="color:#66d9ef">IS</span> <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span> 
<span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> age;

mysql<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">SELECT</span> name, birth, death,
       TIMESTAMPDIFF(<span style="color:#66d9ef">YEAR</span>,birth,death) <span style="color:#66d9ef">AS</span> age
       <span style="color:#66d9ef">FROM</span> pet <span style="color:#66d9ef">WHERE</span> death <span style="color:#66d9ef">IS</span> <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span> <span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> age;
<span style="color:#f92672">+</span><span style="color:#75715e">--------+------------+------------+------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> name   <span style="color:#f92672">|</span> birth      <span style="color:#f92672">|</span> death      <span style="color:#f92672">|</span> age  <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">--------+------------+------------+------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> Bowser <span style="color:#f92672">|</span> <span style="color:#ae81ff">1989</span><span style="color:#f92672">-</span><span style="color:#ae81ff">08</span><span style="color:#f92672">-</span><span style="color:#ae81ff">31</span> <span style="color:#f92672">|</span> <span style="color:#ae81ff">1995</span><span style="color:#f92672">-</span><span style="color:#ae81ff">07</span><span style="color:#f92672">-</span><span style="color:#ae81ff">29</span> <span style="color:#f92672">|</span>    <span style="color:#ae81ff">5</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">--------+------------+------------+------+
</span></code></pre></div><h2 id="模糊查询n个字符的名称">模糊查询N个字符的名称</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">
<span style="color:#75715e">/* 匹配五个字符 */</span>
mysql<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span> <span style="color:#66d9ef">FROM</span> pet <span style="color:#66d9ef">WHERE</span> name <span style="color:#66d9ef">LIKE</span> <span style="color:#e6db74">&#39;_____&#39;</span>;
<span style="color:#f92672">+</span><span style="color:#75715e">-------+--------+---------+------+------------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> name  <span style="color:#f92672">|</span> <span style="color:#66d9ef">owner</span>  <span style="color:#f92672">|</span> species <span style="color:#f92672">|</span> sex  <span style="color:#f92672">|</span> birth      <span style="color:#f92672">|</span> death <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">-------+--------+---------+------+------------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> Claws <span style="color:#f92672">|</span> Gwen   <span style="color:#f92672">|</span> cat     <span style="color:#f92672">|</span> m    <span style="color:#f92672">|</span> <span style="color:#ae81ff">1994</span><span style="color:#f92672">-</span><span style="color:#ae81ff">03</span><span style="color:#f92672">-</span><span style="color:#ae81ff">17</span> <span style="color:#f92672">|</span> <span style="color:#66d9ef">NULL</span>  <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Buffy <span style="color:#f92672">|</span> Harold <span style="color:#f92672">|</span> dog     <span style="color:#f92672">|</span> f    <span style="color:#f92672">|</span> <span style="color:#ae81ff">1989</span><span style="color:#f92672">-</span><span style="color:#ae81ff">05</span><span style="color:#f92672">-</span><span style="color:#ae81ff">13</span> <span style="color:#f92672">|</span> <span style="color:#66d9ef">NULL</span>  <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">-------+--------+---------+------+------------+-------+
</span><span style="color:#75715e"></span>
mysql<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span> <span style="color:#66d9ef">FROM</span> pet <span style="color:#66d9ef">WHERE</span> name REGEXP <span style="color:#e6db74">&#39;^.....$&#39;</span>;
<span style="color:#f92672">+</span><span style="color:#75715e">-------+--------+---------+------+------------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> name  <span style="color:#f92672">|</span> <span style="color:#66d9ef">owner</span>  <span style="color:#f92672">|</span> species <span style="color:#f92672">|</span> sex  <span style="color:#f92672">|</span> birth      <span style="color:#f92672">|</span> death <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">-------+--------+---------+------+------------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> Claws <span style="color:#f92672">|</span> Gwen   <span style="color:#f92672">|</span> cat     <span style="color:#f92672">|</span> m    <span style="color:#f92672">|</span> <span style="color:#ae81ff">1994</span><span style="color:#f92672">-</span><span style="color:#ae81ff">03</span><span style="color:#f92672">-</span><span style="color:#ae81ff">17</span> <span style="color:#f92672">|</span> <span style="color:#66d9ef">NULL</span>  <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Buffy <span style="color:#f92672">|</span> Harold <span style="color:#f92672">|</span> dog     <span style="color:#f92672">|</span> f    <span style="color:#f92672">|</span> <span style="color:#ae81ff">1989</span><span style="color:#f92672">-</span><span style="color:#ae81ff">05</span><span style="color:#f92672">-</span><span style="color:#ae81ff">13</span> <span style="color:#f92672">|</span> <span style="color:#66d9ef">NULL</span>  <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">-------+--------+---------+------+------------+-------+
</span><span style="color:#75715e"></span>
mysql<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span> <span style="color:#66d9ef">FROM</span> pet <span style="color:#66d9ef">WHERE</span> name REGEXP <span style="color:#e6db74">&#39;^.{5}$&#39;</span>;
<span style="color:#f92672">+</span><span style="color:#75715e">-------+--------+---------+------+------------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> name  <span style="color:#f92672">|</span> <span style="color:#66d9ef">owner</span>  <span style="color:#f92672">|</span> species <span style="color:#f92672">|</span> sex  <span style="color:#f92672">|</span> birth      <span style="color:#f92672">|</span> death <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">-------+--------+---------+------+------------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> Claws <span style="color:#f92672">|</span> Gwen   <span style="color:#f92672">|</span> cat     <span style="color:#f92672">|</span> m    <span style="color:#f92672">|</span> <span style="color:#ae81ff">1994</span><span style="color:#f92672">-</span><span style="color:#ae81ff">03</span><span style="color:#f92672">-</span><span style="color:#ae81ff">17</span> <span style="color:#f92672">|</span> <span style="color:#66d9ef">NULL</span>  <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span> Buffy <span style="color:#f92672">|</span> Harold <span style="color:#f92672">|</span> dog     <span style="color:#f92672">|</span> f    <span style="color:#f92672">|</span> <span style="color:#ae81ff">1989</span><span style="color:#f92672">-</span><span style="color:#ae81ff">05</span><span style="color:#f92672">-</span><span style="color:#ae81ff">13</span> <span style="color:#f92672">|</span> <span style="color:#66d9ef">NULL</span>  <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">-------+--------+---------+------+------------+-------+
</span><span style="color:#75715e"></span>

</code></pre></div><h2 id="having">having</h2>
<blockquote>
<p>用于where之后进行二次结果集条件限制</p>
</blockquote>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> <span style="color:#66d9ef">avg</span>(latency) <span style="color:#66d9ef">as</span> avg_latency,projectName <span style="color:#66d9ef">group</span> <span style="color:#66d9ef">by</span> projectName 
<span style="color:#66d9ef">HAVING</span> <span style="color:#66d9ef">avg</span>(latency) <span style="color:#f92672">&gt;</span> <span style="color:#ae81ff">5700000</span>
<span style="color:#66d9ef">order</span> <span style="color:#66d9ef">by</span> avg_latency <span style="color:#66d9ef">desc</span>
</code></pre></div><h2 id="group_by">group_by</h2>
<blockquote>
<p>分类</p>
</blockquote>
<h2 id="inner-join-left-join-right-join">Inner join left join right join</h2>
<blockquote>
<p>多表查询</p>
</blockquote>
<h2 id="distinct">distinct</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#75715e">-- 查询学生的家庭住址(去重)
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> <span style="color:#66d9ef">distinct</span> stuaddr <span style="color:#66d9ef">from</span> tb_student <span style="color:#66d9ef">where</span> stuaddr <span style="color:#66d9ef">is</span> <span style="color:#66d9ef">not</span> <span style="color:#66d9ef">null</span>;
</code></pre></div><h2 id="json函数">json函数</h2>
<p>JSON函数，可以解析一段字符串为JSON类型，并且提取JSON中的字段。JSON主要有两种结构：map和array。如果一个字符串解析成JSON失败，那么返回的是null。</p>
<p>日志服务支持以下常见的JSON函数：</p>
<table>
<thead>
<tr>
<th style="text-align:left">函数名</th>
<th style="text-align:left">含义</th>
<th style="text-align:left">样例</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:left"><code>json_parse(string)</code></td>
<td style="text-align:left">把字符串转化成JSON类型。</td>
<td style="text-align:left"><code>SELECT json_parse('[1, 2, 3]')</code> 结果为JSON类型数组</td>
</tr>
<tr>
<td style="text-align:left"><code>json_format(json)</code></td>
<td style="text-align:left">把JSON类型转化成字符串。</td>
<td style="text-align:left"><code>SELECT json_format(json_parse('[1, 2, 3]')) </code>结果为字符串</td>
</tr>
<tr>
<td style="text-align:left"><code>json_array_contains(json, value)</code></td>
<td style="text-align:left">判断一个JSON类型数值，或者一个字符串（内容是一个JSON数组）是否包含某个值。</td>
<td style="text-align:left"><code>SELECT json_array_contains(json_parse('[1, 2, 3]'), 2)或 SELECT json_array_contains('[1, 2, 3]', 2)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>json_array_get(json_array, index)</code></td>
<td style="text-align:left">同<code>json_array_contains</code>，是获取一个JSON数组的某个下标对应的元素。</td>
<td style="text-align:left"><code>SELECT json_array_get('[&quot;a&quot;, &quot;b&quot;, &quot;c&quot;]', 0)结果为'a'</code></td>
</tr>
<tr>
<td style="text-align:left"><code>json_array_length(json)</code></td>
<td style="text-align:left">返回JSON数组的大小。</td>
<td style="text-align:left"><code>SELECT json_array_length('[1, 2, 3]') 返回结果3</code></td>
</tr>
<tr>
<td style="text-align:left"><code>json_extract(json, json_path)</code></td>
<td style="text-align:left">从一个JSON对象中提取值，JSON路径的语法类似<code>$.store.book[0].title</code>，返回结果是一个JSON对象。</td>
<td style="text-align:left"><code>SELECT json_extract(json, '$.store.book');</code></td>
</tr>
<tr>
<td style="text-align:left"><code>json_extract_scalar(json, json_path)</code></td>
<td style="text-align:left">类似<code>json_extract</code>，但是返回结果是字符串类型。</td>
<td style="text-align:left">-</td>
</tr>
<tr>
<td style="text-align:left"><code>json_size(json,json_path)</code></td>
<td style="text-align:left">获取JSON对象或数组的大小。</td>
<td style="text-align:left"><code>SELECT json_size('[1, 2, 3]') 返回结果3</code></td>
</tr>
</tbody>
</table>
<h2 id="通用聚合函数">通用聚合函数</h2>
<table>
<thead>
<tr>
<th style="text-align:left">语句</th>
<th style="text-align:left">含义</th>
<th style="text-align:left">示例</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:left"><code>arbitrary(x)</code></td>
<td style="text-align:left">随机返回x列中的一个值。</td>
<td style="text-align:left"><code>latency &gt; 100 | select arbitrary(method)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>avg(x)</code></td>
<td style="text-align:left">计算x列的算数平均值。</td>
<td style="text-align:left"><code>latency &gt; 100 | select avg(latency)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>checksum(x)</code></td>
<td style="text-align:left">计算某一列的checksum，返回base64编码。</td>
<td style="text-align:left"><code>latency &gt; 100 | select checksum(method)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>count(*)</code></td>
<td style="text-align:left">表示所有的行数。</td>
<td style="text-align:left">-</td>
</tr>
<tr>
<td style="text-align:left"><code>count(x)</code></td>
<td style="text-align:left">计算某一列非null的个数。</td>
<td style="text-align:left"><code>latency &gt; 100 | select count(method) </code></td>
</tr>
<tr>
<td style="text-align:left"><code>count(数字)</code></td>
<td style="text-align:left"><code>count(数字)</code>，如<code>count(1)</code>，等同于<code>count(*)</code>，表示所有的行数。</td>
<td style="text-align:left">-</td>
</tr>
<tr>
<td style="text-align:left"><code>count_if(x)</code></td>
<td style="text-align:left">计算x=true的个数。</td>
<td style="text-align:left"><code>latency &gt; 100 | select count_if(url like ‘%abc’) </code></td>
</tr>
<tr>
<td style="text-align:left"><code>geometric_mean(x)</code></td>
<td style="text-align:left">计算某一列的几何平均数。</td>
<td style="text-align:left"><code>latency &gt; 100 | select geometric_mean(latency)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>max_by(x,y)</code></td>
<td style="text-align:left">返回当y取最大值时，x当前的值。</td>
<td style="text-align:left">查询延时最高的时候，对应的method：<code>latency&gt;100 | select max_by(method,latency)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>max_by(x,y,n)</code></td>
<td style="text-align:left">返回y最高的n行，对应的x的值。</td>
<td style="text-align:left">查询延时最高的3行，对应的method：<code>latency &gt; 100 | select max_by(method,latency,3)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>min_by(x,y)</code></td>
<td style="text-align:left">返回当y取最小值时，x当前的值。</td>
<td style="text-align:left">查询延时最低的请求，对应的method：<code>* | select min_by(x,y)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>min_by(x,y,n)</code></td>
<td style="text-align:left">返回y最小的n行，对应的x的值。</td>
<td style="text-align:left">查询延时最小的3行，对应的method： <code>* | select min_by(method,latency,3)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>max(x)</code></td>
<td style="text-align:left">返回最大值。</td>
<td style="text-align:left"><code>latency &gt; 100| select max(inflow)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>min(x)</code></td>
<td style="text-align:left">返回最小值。</td>
<td style="text-align:left"><code>latency &gt; 100| select min(inflow)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>sum(x)</code></td>
<td style="text-align:left">返回x列的和。</td>
<td style="text-align:left"><code>latency &gt; 10 | select sum(inflow)</code></td>
</tr>
<tr>
<td style="text-align:left"><code>bitwise_and_agg(x)</code></td>
<td style="text-align:left">对某一列的所有数值做and计算。</td>
<td style="text-align:left">-</td>
</tr>
<tr>
<td style="text-align:left"><code>bitwise_or_agg(x)</code></td>
<td style="text-align:left">对某一列的数值做or计算。</td>
<td style="text-align:left">-</td>
</tr>
</tbody>
</table>
<h2 id="安全检测函数">安全检测函数</h2>
<h3 id="函数列表">函数列表</h3>
<table>
<thead>
<tr>
<th style="text-align:left">函数名</th>
<th style="text-align:left">含义</th>
<th style="text-align:left">样例</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:left">security_check_ip</td>
<td style="text-align:left">检查IP是否安全，其中： 返回1：命中，表示不安全返回0：未命中</td>
<td style="text-align:left"><code>select security_check_ip(real_client_ip)</code></td>
</tr>
<tr>
<td style="text-align:left">security_check_domain</td>
<td style="text-align:left">检查Domain是否安全，其中： 返回1：命中，表示不安全返回0：未命中</td>
<td style="text-align:left"><code>select security_check_domain(site)</code></td>
</tr>
<tr>
<td style="text-align:left">security_check_url</td>
<td style="text-align:left">检查URL是否安全，其中： 返回1：命中，表示不安全返回0：未命中</td>
<td style="text-align:left"><code>select security_check_domain(concat(host, url))</code></td>
</tr>
</tbody>
</table>
<h2 id="批量文件处理">批量文件处理</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-python" data-lang="python">mysql<span style="color:#f92672">&gt;</span> source filename;
mysql<span style="color:#f92672">&gt;</span> \<span style="color:#f92672">.</span> filename
</code></pre></div><h2 id="保留某一列最大值">保留某一列最大值</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span> article, dealer, price
<span style="color:#66d9ef">FROM</span>   shop
<span style="color:#66d9ef">WHERE</span>  price<span style="color:#f92672">=</span>(<span style="color:#66d9ef">SELECT</span> <span style="color:#66d9ef">MAX</span>(price) <span style="color:#66d9ef">FROM</span> shop);

<span style="color:#f92672">+</span><span style="color:#75715e">---------+--------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> article <span style="color:#f92672">|</span> dealer <span style="color:#f92672">|</span> price <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">---------+--------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span>    <span style="color:#ae81ff">0004</span> <span style="color:#f92672">|</span> D      <span style="color:#f92672">|</span> <span style="color:#ae81ff">19</span>.<span style="color:#ae81ff">95</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">---------+--------+-------+
</span><span style="color:#75715e"></span>

<span style="color:#66d9ef">SELECT</span> s1.article, s1.dealer, s1.price
<span style="color:#66d9ef">FROM</span> shop s1
<span style="color:#66d9ef">LEFT</span> <span style="color:#66d9ef">JOIN</span> shop s2 <span style="color:#66d9ef">ON</span> s1.price <span style="color:#f92672">&lt;</span> s2.price
<span style="color:#66d9ef">WHERE</span> s2.article <span style="color:#66d9ef">IS</span> <span style="color:#66d9ef">NULL</span>;

<span style="color:#66d9ef">SELECT</span> article, dealer, price
<span style="color:#66d9ef">FROM</span> shop
<span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> price <span style="color:#66d9ef">DESC</span>
<span style="color:#66d9ef">LIMIT</span> <span style="color:#ae81ff">1</span>;
</code></pre></div><h2 id="包含某列的分组最大值的行">包含某列的分组最大值的行</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">
<span style="color:#66d9ef">SELECT</span> article, dealer, price
<span style="color:#66d9ef">FROM</span>   shop s1
<span style="color:#66d9ef">WHERE</span>  price<span style="color:#f92672">=</span>(<span style="color:#66d9ef">SELECT</span> <span style="color:#66d9ef">MAX</span>(s2.price)
              <span style="color:#66d9ef">FROM</span> shop s2
              <span style="color:#66d9ef">WHERE</span> s1.article <span style="color:#f92672">=</span> s2.article)
<span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> article;

<span style="color:#f92672">+</span><span style="color:#75715e">---------+--------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> article <span style="color:#f92672">|</span> dealer <span style="color:#f92672">|</span> price <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">---------+--------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span>    <span style="color:#ae81ff">0001</span> <span style="color:#f92672">|</span> B      <span style="color:#f92672">|</span>  <span style="color:#ae81ff">3</span>.<span style="color:#ae81ff">99</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>    <span style="color:#ae81ff">0002</span> <span style="color:#f92672">|</span> A      <span style="color:#f92672">|</span> <span style="color:#ae81ff">10</span>.<span style="color:#ae81ff">99</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>    <span style="color:#ae81ff">0003</span> <span style="color:#f92672">|</span> <span style="color:#66d9ef">C</span>      <span style="color:#f92672">|</span>  <span style="color:#ae81ff">1</span>.<span style="color:#ae81ff">69</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>    <span style="color:#ae81ff">0004</span> <span style="color:#f92672">|</span> D      <span style="color:#f92672">|</span> <span style="color:#ae81ff">19</span>.<span style="color:#ae81ff">95</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">---------+--------+-------+
</span><span style="color:#75715e"></span>

<span style="color:#66d9ef">SELECT</span> s1.article, dealer, s1.price
<span style="color:#66d9ef">FROM</span> shop s1
<span style="color:#66d9ef">JOIN</span> (
  <span style="color:#66d9ef">SELECT</span> article, <span style="color:#66d9ef">MAX</span>(price) <span style="color:#66d9ef">AS</span> price
  <span style="color:#66d9ef">FROM</span> shop
  <span style="color:#66d9ef">GROUP</span> <span style="color:#66d9ef">BY</span> article) <span style="color:#66d9ef">AS</span> s2
  <span style="color:#66d9ef">ON</span> s1.article <span style="color:#f92672">=</span> s2.article <span style="color:#66d9ef">AND</span> s1.price <span style="color:#f92672">=</span> s2.price
<span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> article;

<span style="color:#66d9ef">SELECT</span> s1.article, s1.dealer, s1.price
<span style="color:#66d9ef">FROM</span> shop s1
<span style="color:#66d9ef">LEFT</span> <span style="color:#66d9ef">JOIN</span> shop s2 <span style="color:#66d9ef">ON</span> s1.article <span style="color:#f92672">=</span> s2.article <span style="color:#66d9ef">AND</span> s1.price <span style="color:#f92672">&lt;</span> s2.price
<span style="color:#66d9ef">WHERE</span> s2.article <span style="color:#66d9ef">IS</span> <span style="color:#66d9ef">NULL</span>
<span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> s1.article;
</code></pre></div><h2 id="自定义变量">自定义变量</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">mysql<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">@</span>min_price:<span style="color:#f92672">=</span><span style="color:#66d9ef">MIN</span>(price),<span style="color:#f92672">@</span>max_price:<span style="color:#f92672">=</span><span style="color:#66d9ef">MAX</span>(price) <span style="color:#66d9ef">FROM</span> shop;
mysql<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span> <span style="color:#66d9ef">FROM</span> shop <span style="color:#66d9ef">WHERE</span> price<span style="color:#f92672">=@</span>min_price <span style="color:#66d9ef">OR</span> price<span style="color:#f92672">=@</span>max_price;
<span style="color:#f92672">+</span><span style="color:#75715e">---------+--------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> article <span style="color:#f92672">|</span> dealer <span style="color:#f92672">|</span> price <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">---------+--------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span>    <span style="color:#ae81ff">0003</span> <span style="color:#f92672">|</span> D      <span style="color:#f92672">|</span>  <span style="color:#ae81ff">1</span>.<span style="color:#ae81ff">25</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>    <span style="color:#ae81ff">0004</span> <span style="color:#f92672">|</span> D      <span style="color:#f92672">|</span> <span style="color:#ae81ff">19</span>.<span style="color:#ae81ff">95</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">---------+--------+-------+
</span></code></pre></div><h2 id="使用外键">使用外键</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">
<span style="color:#66d9ef">CREATE</span> <span style="color:#66d9ef">TABLE</span> person (
    id SMALLINT UNSIGNED <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span> AUTO_INCREMENT,
    name CHAR(<span style="color:#ae81ff">60</span>) <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span>,
    <span style="color:#66d9ef">PRIMARY</span> <span style="color:#66d9ef">KEY</span> (id)
);

<span style="color:#66d9ef">CREATE</span> <span style="color:#66d9ef">TABLE</span> shirt (
    id SMALLINT UNSIGNED <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span> AUTO_INCREMENT,
    style ENUM(<span style="color:#e6db74">&#39;t-shirt&#39;</span>, <span style="color:#e6db74">&#39;polo&#39;</span>, <span style="color:#e6db74">&#39;dress&#39;</span>) <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span>,
    color ENUM(<span style="color:#e6db74">&#39;red&#39;</span>, <span style="color:#e6db74">&#39;blue&#39;</span>, <span style="color:#e6db74">&#39;orange&#39;</span>, <span style="color:#e6db74">&#39;white&#39;</span>, <span style="color:#e6db74">&#39;black&#39;</span>) <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span>,
    <span style="color:#66d9ef">owner</span> SMALLINT UNSIGNED <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span> <span style="color:#66d9ef">REFERENCES</span> person(id),
    <span style="color:#66d9ef">PRIMARY</span> <span style="color:#66d9ef">KEY</span> (id)
);

<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> person <span style="color:#66d9ef">VALUES</span> (<span style="color:#66d9ef">NULL</span>, <span style="color:#e6db74">&#39;Antonio Paz&#39;</span>);

<span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">@</span><span style="color:#66d9ef">last</span> :<span style="color:#f92672">=</span> LAST_INSERT_ID();

<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> shirt <span style="color:#66d9ef">VALUES</span>
(<span style="color:#66d9ef">NULL</span>, <span style="color:#e6db74">&#39;polo&#39;</span>, <span style="color:#e6db74">&#39;blue&#39;</span>, <span style="color:#f92672">@</span><span style="color:#66d9ef">last</span>),
(<span style="color:#66d9ef">NULL</span>, <span style="color:#e6db74">&#39;dress&#39;</span>, <span style="color:#e6db74">&#39;white&#39;</span>, <span style="color:#f92672">@</span><span style="color:#66d9ef">last</span>),
(<span style="color:#66d9ef">NULL</span>, <span style="color:#e6db74">&#39;t-shirt&#39;</span>, <span style="color:#e6db74">&#39;blue&#39;</span>, <span style="color:#f92672">@</span><span style="color:#66d9ef">last</span>);

<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> person <span style="color:#66d9ef">VALUES</span> (<span style="color:#66d9ef">NULL</span>, <span style="color:#e6db74">&#39;Lilliana Angelovska&#39;</span>);

<span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">@</span><span style="color:#66d9ef">last</span> :<span style="color:#f92672">=</span> LAST_INSERT_ID();

<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> shirt <span style="color:#66d9ef">VALUES</span>
(<span style="color:#66d9ef">NULL</span>, <span style="color:#e6db74">&#39;dress&#39;</span>, <span style="color:#e6db74">&#39;orange&#39;</span>, <span style="color:#f92672">@</span><span style="color:#66d9ef">last</span>),
(<span style="color:#66d9ef">NULL</span>, <span style="color:#e6db74">&#39;polo&#39;</span>, <span style="color:#e6db74">&#39;red&#39;</span>, <span style="color:#f92672">@</span><span style="color:#66d9ef">last</span>),
(<span style="color:#66d9ef">NULL</span>, <span style="color:#e6db74">&#39;dress&#39;</span>, <span style="color:#e6db74">&#39;blue&#39;</span>, <span style="color:#f92672">@</span><span style="color:#66d9ef">last</span>),
(<span style="color:#66d9ef">NULL</span>, <span style="color:#e6db74">&#39;t-shirt&#39;</span>, <span style="color:#e6db74">&#39;white&#39;</span>, <span style="color:#f92672">@</span><span style="color:#66d9ef">last</span>);

<span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span> <span style="color:#66d9ef">FROM</span> person;
<span style="color:#f92672">+</span><span style="color:#75715e">----+---------------------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> id <span style="color:#f92672">|</span> name                <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">----+---------------------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span>  <span style="color:#ae81ff">1</span> <span style="color:#f92672">|</span> Antonio Paz         <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>  <span style="color:#ae81ff">2</span> <span style="color:#f92672">|</span> Lilliana Angelovska <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">----+---------------------+
</span><span style="color:#75715e"></span>
<span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span> <span style="color:#66d9ef">FROM</span> shirt;
<span style="color:#f92672">+</span><span style="color:#75715e">----+---------+--------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> id <span style="color:#f92672">|</span> style   <span style="color:#f92672">|</span> color  <span style="color:#f92672">|</span> <span style="color:#66d9ef">owner</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">----+---------+--------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span>  <span style="color:#ae81ff">1</span> <span style="color:#f92672">|</span> polo    <span style="color:#f92672">|</span> blue   <span style="color:#f92672">|</span>     <span style="color:#ae81ff">1</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>  <span style="color:#ae81ff">2</span> <span style="color:#f92672">|</span> dress   <span style="color:#f92672">|</span> white  <span style="color:#f92672">|</span>     <span style="color:#ae81ff">1</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>  <span style="color:#ae81ff">3</span> <span style="color:#f92672">|</span> t<span style="color:#f92672">-</span>shirt <span style="color:#f92672">|</span> blue   <span style="color:#f92672">|</span>     <span style="color:#ae81ff">1</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>  <span style="color:#ae81ff">4</span> <span style="color:#f92672">|</span> dress   <span style="color:#f92672">|</span> orange <span style="color:#f92672">|</span>     <span style="color:#ae81ff">2</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>  <span style="color:#ae81ff">5</span> <span style="color:#f92672">|</span> polo    <span style="color:#f92672">|</span> red    <span style="color:#f92672">|</span>     <span style="color:#ae81ff">2</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>  <span style="color:#ae81ff">6</span> <span style="color:#f92672">|</span> dress   <span style="color:#f92672">|</span> blue   <span style="color:#f92672">|</span>     <span style="color:#ae81ff">2</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>  <span style="color:#ae81ff">7</span> <span style="color:#f92672">|</span> t<span style="color:#f92672">-</span>shirt <span style="color:#f92672">|</span> white  <span style="color:#f92672">|</span>     <span style="color:#ae81ff">2</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">----+---------+--------+-------+
</span><span style="color:#75715e"></span>
<span style="color:#66d9ef">SELECT</span> s.<span style="color:#f92672">*</span> <span style="color:#66d9ef">FROM</span> person p <span style="color:#66d9ef">INNER</span> <span style="color:#66d9ef">JOIN</span> shirt s
   <span style="color:#66d9ef">ON</span> s.<span style="color:#66d9ef">owner</span> <span style="color:#f92672">=</span> p.id
 <span style="color:#66d9ef">WHERE</span> p.name <span style="color:#66d9ef">LIKE</span> <span style="color:#e6db74">&#39;Lilliana%&#39;</span>
   <span style="color:#66d9ef">AND</span> s.color <span style="color:#f92672">&lt;&gt;</span> <span style="color:#e6db74">&#39;white&#39;</span>;

<span style="color:#f92672">+</span><span style="color:#75715e">----+-------+--------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span> id <span style="color:#f92672">|</span> style <span style="color:#f92672">|</span> color  <span style="color:#f92672">|</span> <span style="color:#66d9ef">owner</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">----+-------+--------+-------+
</span><span style="color:#75715e"></span><span style="color:#f92672">|</span>  <span style="color:#ae81ff">4</span> <span style="color:#f92672">|</span> dress <span style="color:#f92672">|</span> orange <span style="color:#f92672">|</span>     <span style="color:#ae81ff">2</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>  <span style="color:#ae81ff">5</span> <span style="color:#f92672">|</span> polo  <span style="color:#f92672">|</span> red    <span style="color:#f92672">|</span>     <span style="color:#ae81ff">2</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">|</span>  <span style="color:#ae81ff">6</span> <span style="color:#f92672">|</span> dress <span style="color:#f92672">|</span> blue   <span style="color:#f92672">|</span>     <span style="color:#ae81ff">2</span> <span style="color:#f92672">|</span>
<span style="color:#f92672">+</span><span style="color:#75715e">----+-------+--------+-------+
</span><span style="color:#75715e"></span>

<span style="color:#66d9ef">show</span> <span style="color:#66d9ef">create</span> <span style="color:#66d9ef">table</span> shirt<span style="color:#960050;background-color:#1e0010">\</span><span style="color:#66d9ef">G</span>;
</code></pre></div><h2 id="一种棘手的情况是搜索结合orhttpswwwdocs4devcomdocszhmysql57referencelogical-operatorshtmloperator_or的两个不同键">一种棘手的情况是搜索结合<a href="https://www.docs4dev.com/docs/zh/mysql/5.7/reference/logical-operators.html#operator_or">OR</a>的两个不同键：</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#75715e">-- 优化前
</span><span style="color:#75715e"></span>
<span style="color:#66d9ef">SELECT</span> field1_index, field2_index <span style="color:#66d9ef">FROM</span> test_table
<span style="color:#66d9ef">WHERE</span> field1_index <span style="color:#f92672">=</span> <span style="color:#e6db74">&#39;1&#39;</span> <span style="color:#66d9ef">OR</span>  field2_index <span style="color:#f92672">=</span> <span style="color:#e6db74">&#39;1&#39;</span>


<span style="color:#75715e">-- 优化后
</span><span style="color:#75715e"></span>
<span style="color:#66d9ef">SELECT</span> field1_index, field2_index
    <span style="color:#66d9ef">FROM</span> test_table <span style="color:#66d9ef">WHERE</span> field1_index <span style="color:#f92672">=</span> <span style="color:#e6db74">&#39;1&#39;</span>
<span style="color:#66d9ef">UNION</span>
<span style="color:#66d9ef">SELECT</span> field1_index, field2_index
    <span style="color:#66d9ef">FROM</span> test_table <span style="color:#66d9ef">WHERE</span> field2_index <span style="color:#f92672">=</span> <span style="color:#e6db74">&#39;1&#39;</span>;
</code></pre></div><h2 id="dcl">dcl</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#75715e">-- 创建可以远程登录的root账号并为其指定口令
</span><span style="color:#75715e"></span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">user</span> <span style="color:#e6db74">&#39;root&#39;</span><span style="color:#f92672">@</span><span style="color:#e6db74">&#39;%&#39;</span> identified <span style="color:#66d9ef">by</span> <span style="color:#e6db74">&#39;123456&#39;</span>;

<span style="color:#75715e">-- 为远程登录的root账号授权操作所有数据库所有对象的所有权限并允许其将权限再次赋予其他用户
</span><span style="color:#75715e"></span><span style="color:#66d9ef">grant</span> <span style="color:#66d9ef">all</span> <span style="color:#66d9ef">privileges</span> <span style="color:#66d9ef">on</span> <span style="color:#f92672">*</span>.<span style="color:#f92672">*</span> <span style="color:#66d9ef">to</span> <span style="color:#e6db74">&#39;root&#39;</span><span style="color:#f92672">@</span><span style="color:#e6db74">&#39;%&#39;</span> <span style="color:#66d9ef">with</span> <span style="color:#66d9ef">grant</span> <span style="color:#66d9ef">option</span>;

<span style="color:#75715e">-- 创建名为hellokitty的用户并为其指定口令
</span><span style="color:#75715e"></span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">user</span> <span style="color:#e6db74">&#39;hellokitty&#39;</span><span style="color:#f92672">@</span><span style="color:#e6db74">&#39;%&#39;</span> identified <span style="color:#66d9ef">by</span> <span style="color:#e6db74">&#39;123123&#39;</span>;

<span style="color:#75715e">-- 将对school数据库所有对象的所有操作权限授予hellokitty
</span><span style="color:#75715e"></span><span style="color:#66d9ef">grant</span> <span style="color:#66d9ef">all</span> <span style="color:#66d9ef">privileges</span> <span style="color:#66d9ef">on</span> school.<span style="color:#f92672">*</span> <span style="color:#66d9ef">to</span> <span style="color:#e6db74">&#39;hellokitty&#39;</span><span style="color:#f92672">@</span><span style="color:#e6db74">&#39;%&#39;</span>;

<span style="color:#75715e">-- 召回hellokitty对school数据库所有对象的insert/delete/update权限
</span><span style="color:#75715e"></span><span style="color:#66d9ef">revoke</span> <span style="color:#66d9ef">insert</span>, <span style="color:#66d9ef">delete</span>, <span style="color:#66d9ef">update</span> <span style="color:#66d9ef">on</span> school.<span style="color:#f92672">*</span> <span style="color:#66d9ef">from</span> <span style="color:#e6db74">&#39;hellokitty&#39;</span><span style="color:#f92672">@</span><span style="color:#e6db74">&#39;%&#39;</span>;
</code></pre></div><hr>
<p>参考内容: <a href="https://www.docs4dev.com/docs/zh/mysql/5.7/reference/batch-mode.html">https://www.docs4dev.com/docs/zh/mysql/5.7/reference/batch-mode.html</a></p>
<p>参考内容: <a href="https://github.com/jackfrued/Python-100-Days/blob/master/Day36-40/36-38">https://github.com/jackfrued/Python-100-Days/blob/master/Day36-40/36-38</a>.关系型数据库MySQL.md</p>
    <div class = 'post_extra'><div class = 'copy' data-share = 'Share Story' data-copied = 'Link Copied'>
  <svg>
    <use xlink:href="#copy"></use>
  </svg>  
</div>

    </div>

  </article>
  <div class="post-comment">
       
       <h3>Comments welcome !</h3>



<div id="vcomments"></div>
<script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
<script src='//unpkg.com/valine/dist/Valine.min.js'></script>
 <script type="text/javascript">
   new Valine({
       el: '#vcomments' ,
       appId: 'xb5Xl8O60fXEK4d13KsazkkL-gzGzoHsz',
       appKey: 'qyztiJ2tgf6AhvsrH088N29o',
       notify: 'false', 
       verify: 'false', 
       avatar:'mm', 
       placeholder: '说说你的看法吧...',
       visitor: 'true'
   });
 </script>
  </div>
  <aside><h3></h3>
<ul class='posts aside'>
<li class = 'post_item'>
  <a class = 'post_card' href='https://jxiaof.com/post/first/' title = 'ABOUT MYSELF' style = 'background-image: url(https://jxiaof.com/images/speakers.jpg);'>
  </a>
  <div class = 'excerpt'>
    <div class = 'excerpt_meta'><div class = 'copy' data-share = 'Share Story' data-copied = 'Link Copied'>
  <svg>
    <use xlink:href="#copy"></use>
  </svg>  
</div>

    </div>
    <h3 class = 'post_link'>
      <a href='https://jxiaof.com/post/first/'>ABOUT MYSELF</a>
    </h3>
    <p class = 'pale'><p>​        I am a light game enthusiast with poor gastrointestinal function. I go to bed …</p></p>
  </div>
</li>

<li class = 'post_item'>
  <a class = 'post_card' href='https://jxiaof.com/post/leetcode/' title = 'Leetcode' style = 'background-image: url(https://jxiaof.com/images/programmer.png);'>
  </a>
  <div class = 'excerpt'>
    <div class = 'excerpt_meta'><div class = 'copy' data-share = 'Share Story' data-copied = 'Link Copied'>
  <svg>
    <use xlink:href="#copy"></use>
  </svg>  
</div>

    </div>
    <h3 class = 'post_link'>
      <a href='https://jxiaof.com/post/leetcode/'>Leetcode</a>
    </h3>
    <p class = 'pale'>买卖股票简答:
class Solution: def maxProfit(self, prices): &#34;&#34;&#34; :type prices: …</p>
  </div>
</li>

</ul>

  </aside>
</div>
<script src = 'https://jxiaof.com/js/autosize.min.js'></script>
<script src = 'https://jxiaof.com/js/timeago.js'></script>
    </main><svg width="0" height="0" class="hidden">
  <symbol viewBox="0 0 699.428 699.428" xmlns="http://www.w3.org/2000/svg" id="copy">
    <path d="M502.714 0H240.428C194.178 0 153 42.425 153 87.429l-25.267.59c-46.228 0-84.019 41.834-84.019 86.838V612c0 45.004 41.179 87.428 87.429 87.428H459c46.249 0 87.428-42.424 87.428-87.428h21.857c46.25 0 87.429-42.424 87.429-87.428v-349.19zM459 655.715H131.143c-22.95 0-43.714-21.441-43.714-43.715V174.857c0-22.272 18.688-42.993 41.638-42.993l23.933-.721v393.429C153 569.576 194.178 612 240.428 612h262.286c0 22.273-20.765 43.715-43.714 43.715zm153-131.143c0 22.271-20.765 43.713-43.715 43.713H240.428c-22.95 0-43.714-21.441-43.714-43.713V87.429c0-22.272 20.764-43.714 43.714-43.714H459c-.351 50.337 0 87.975 0 87.975 0 45.419 40.872 86.882 87.428 86.882H612zm-65.572-349.715c-23.277 0-43.714-42.293-43.714-64.981V44.348L612 174.857zm-43.714 131.537H306c-12.065 0-21.857 9.77-21.857 21.835s9.792 21.835 21.857 21.835h196.714c12.065 0 21.857-9.771 21.857-21.835 0-12.065-9.792-21.835-21.857-21.835zm0 109.176H306c-12.065 0-21.857 9.77-21.857 21.834 0 12.066 9.792 21.836 21.857 21.836h196.714c12.065 0 21.857-9.77 21.857-21.836 0-12.064-9.792-21.834-21.857-21.834z"
    ></path>
  </symbol>
  <symbol viewBox="0 0 60.015 60.015" xmlns="http://www.w3.org/2000/svg" id="reply">
    <path d="M42.007 0h-24c-9.925 0-18 8.075-18 18v14c0 9.59 7.538 17.452 17 17.973v8.344a1.694 1.694 0 0 0 1.699 1.698c.44 0 .873-.173 1.198-.498l1.876-1.876C26.708 52.713 33.259 50 40.227 50h1.78c9.925 0 18-8.075 18-18V18c0-9.925-8.075-18-18-18zm16 32c0 8.822-7.178 16-16 16h-1.78c-7.502 0-14.556 2.921-19.86 8.226l-1.359 1.359V44a1 1 0 1 0-2 0v3.949c-8.356-.52-15-7.465-15-15.949V18c0-8.822 7.178-16 16-16h24c8.822 0 16 7.178 16 16v14z"></path>
  </symbol>
</svg>
<footer class = 'footer'>
  <div class = 'footer_inner wrap pale'>
    <p>&copy;&nbsp;<span class = 'year'></span>&nbsp;江小凡的博客.
    Designed by  <a href = 'https://www.github.com/jxiaof' title = 'Linkedin Profile'>江小凡</a></p>
  </div>
</footer>
<script src = 'https://jxiaof.com/js/index.min.c3b0a769e56310d4359f30762ab615db8cd6ce33d555ee7f68d25e8a40c63ce18bf6b273d35a9b5c033a7f6e71b1d424e74d9f542d93482215c671b3f31f885f.js'></script>

  </body>
</html>
